<?php
include APPLICATION_PATH . "/models/Report3.php";
include APPLICATION_PATH . "/models/Admin_Area.php";
include APPLICATION_PATH . "/models/Report4.php";
include APPLICATION_PATH . "/models/Report5.php";
/* 
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
class Kinhdoanh_Report3Controller extends Zend_Controller_Action{
    public function init(){       
        $bootstrap = $this->getInvokeArg("bootstrap");
        $this->aConfig = $bootstrap->getOptions();
        $this->view->aConfig = $this->aConfig;
        //$this->modelMapper= new Model_ProvinceMapper();
        $this->model= new Model_Report3();
        $this->report4model= new Model_Report4();
        $this->report5model= new Model_Report5();
        $this->Admin_AreamodelMapper = new Model_Admin_AreaMapper();
        $this->Admin_Areamodel = new Model_Admin_Area();        
        $this->kinhdoanh_id = GlobalLib::getLoginId();
    }
    public function indexAction(){
       $this->view->controller = $this;
       $this->view->fromdate ="";
       $this->view->todate ="";
       $this->view->product_id = "";
       $this->view->groupproduct_id = "";
       $this->view->agentbuyer_id = "";
       $this->view->agentseller_id = "";
       if($this->getRequest()->isPost()){
            $this->view->fromdate = $_POST["from_date"];
            $this->view->todate = $_POST["to_date"];
            if (isset($_POST['product_id'])){
                $product_id = array();
                foreach ($_POST['product_id'] as $key => $value) {
                    array_push($product_id,$value);                
                }            
                $this->view->product_id = implode(",", $product_id);
            }
            if (isset($_POST['groupproduct_id'])){
                $groupproduct_id = array();
                foreach ($_POST['groupproduct_id'] as $key => $value) {
                    array_push($groupproduct_id,$value);                
                }            
                $this->view->groupproduct_id = implode(",", $groupproduct_id);
            }
            if (isset($_POST['agentbuyer_id'])){
                $agentbuyer_id = array();
                foreach ($_POST['agentbuyer_id'] as $key => $value) {
                    array_push($agentbuyer_id,$value);                
                }            
                $this->view->agentbuyer_id = implode(",", $agentbuyer_id);
            }
            if (isset($_POST['agentseller_id'])){
                $agentseller_id = array();
                foreach ($_POST['agentseller_id'] as $key => $value) {
                    array_push($agentseller_id,$value);                
                }            
                $this->view->agentseller_id = implode(",", $agentseller_id);
            }
       }
       
       $this->view->productHTML=  GlobalLib::getComboMulti("product_id", "product", "id", "name",NULL,$this->view->product_id,true);
       $this->view->groupproductHTML=  GlobalLib::getComboMulti("groupproduct_id", "product_category", "id", "name",NULL,$this->view->groupproduct_id,true);
       
       //Xu ly filter id danh sach DL/CH cửa kinh doanh
       $arr_buyer = array();
       $arr_seller = array();
       array_push($arr_buyer, 0);
       array_push($arr_seller, 0);
       foreach ($this->model->fetchAllReportWithId($this->view->fromdate, $this->view->todate) as $items) 
        {            
            foreach($this->Admin_AreamodelMapper->fetchAll() as $filter)                
            {    
                if($filter->getAdmin_Id()==$this->kinhdoanh_id && $filter->getArea_Id()==$items->buyer_province)
                //$entry = new Model_Province();
                {
                    array_push($arr_buyer, $items->buyer_id);
                    array_push($arr_seller, $items->seller_id);                    
                }
            }
        }   
        
       $this->view->agentbuyerHTML=  GlobalLib::getComboMulti("agentbuyer_id", "agent", "id", "name","address",$this->view->agentbuyer_id,true,"where id IN (".implode(",", $arr_buyer).")");
       $this->view->agentsellerHTML=  GlobalLib::getComboMulti("agentseller_id", "agent", "id", "name","address",$this->view->agentseller_id,true,"where id IN (".implode(",", $arr_seller).")");
       
       //$_tofrom = $this->_getParam("datefrom","");
        //$this->view->item=$this->model->fetchAllReport();
        //Zend_Debug::dump($this->model->fetchAllReport());
    }
    public function serviceAction(){
        $this->view->controller = $this;        
        $this->_helper->layout->disableLayout();
        $fromdate = $this->_getParam("fromdate","");
        $todate = $this->_getParam("todate","");
        $product_id=$this->_getParam("product_id","");
        $groupproduct_id=$this->_getParam("groupproduct_id","");
        $agentbuyer_id=$this->_getParam("agentbuyer_id","");
        $agentseller_id=$this->_getParam("agentseller_id","");
        $menber   = array();
        foreach ($this->model->fetchAllReport($fromdate, $todate, $product_id, $groupproduct_id, $agentbuyer_id, $agentseller_id) as $items) 
        {            
            foreach($this->Admin_AreamodelMapper->fetchAll() as $filter)                
            {    
                if($filter->getAdmin_Id()==$this->kinhdoanh_id && $filter->getArea_Id()==$items->buyer_province)
                //$entry = new Model_Province();
                {
                    $order_created = GlobalLib::viewDate($items->order_created);
                    $arr = array(
                        'buyer_name' => $items->buyer_name,
                        'seller_name' => $items->seller_name,
                        'product_name' => $items->product_name,
                        'province_name' => $items->province_name,
                        'quantity' => $items->quantity,
                        'charges' => $items->charges,
                        'order_created' => $order_created
                    );
                    array_push($menber, $arr);
                    //$entries[] = $entry;
                }
            }
        }        
        echo json_encode($menber);        
        exit();
    }
    public function exportAction(){
        $this->_helper->layout->disableLayout();
        if($this->getRequest()->isPost()){
            $cat= $this->_getParam("type","");
            if($cat == 1){
           $fromdate=$this->_getParam("fromdate","");
           $todate=$this->_getParam("todate","");
           $product_id=$this->_getParam("product_id","");
            $groupproduct_id=$this->_getParam("groupproduct_id","");
            $agentbuyer_id=$this->_getParam("agentbuyer_id","");
            $agentseller_id=$this->_getParam("agentseller_id","");
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            $rowCount = 4;
            $stt=1;
            $objPHPExcel->getActiveSheet()->setCellValue('A3', "STT");
            $objPHPExcel->getActiveSheet()->setCellValue('B3', "Tên Khách hàng");
            $objPHPExcel->getActiveSheet()->setCellValue('C3', "Khu Vực(Tỉnh)");
            $objPHPExcel->getActiveSheet()->setCellValue('D3', "Khối lượng");
            $objPHPExcel->getActiveSheet()->setCellValue('E3', "Loại Hàng");
            $objPHPExcel->getActiveSheet()->setCellValue('F3', "Thời Điểm");
            $objPHPExcel->getActiveSheet()->setCellValue('G3', "Nguồn(Mua Của ĐL/CH)");
            $objPHPExcel->getActiveSheet()->setCellValue('H3', "Điểm");
            $objPHPExcel->getActiveSheet()->setCellValue('I3', "Ghi Chú");
           
            foreach ($this->model->fetchAllReportExport($fromdate, $todate, $product_id, $groupproduct_id, $agentbuyer_id, $agentseller_id) as $items) {
                foreach ($this->Admin_AreamodelMapper->fetchAll() as $filter) {
                    if ($filter->getAdmin_Id() == $this->kinhdoanh_id && $filter->getArea_Id() == $items->buyer_province) {
                    //$entry = new Model_Province();
                        $order_created = GlobalLib::viewDate($items->order_created);
                        $t_day = substr($order_created, 0, 2);
                        $t_month = substr($order_created, 3, 2);
                        $t_year = substr($order_created, 6, 4);
                        // Fixed problems with offsets
                        $t_date = PHPExcel_Shared_Date::FormattedPHPToExcel($t_year, $t_month, $t_day);
                        $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, $stt);
                        $objPHPExcel->getActiveSheet()->setCellValue('B' . $rowCount, $items->buyer_name);
                        $objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $items->province_name);
                        $objPHPExcel->getActiveSheet()->setCellValue('D' . $rowCount, $items->quantity);
                        $objPHPExcel->getActiveSheet()->setCellValue('E' . $rowCount, $items->product_name);
                        if ($order_created == '') {
                            $objPHPExcel->getActiveSheet()->setCellValue('F' . $rowCount, "");
                        } else {
                            $objPHPExcel->getActiveSheet()->setCellValue('F' . $rowCount, $t_date);
                        }
                        $objPHPExcel->getActiveSheet()->setCellValue('G' . $rowCount, $items->seller_name);
                        $objPHPExcel->getActiveSheet()->setCellValue('H' . $rowCount, $items->charges);
                        $rowCount++;
                        $stt++;
                    }
                }
            }
            $urlimage = GlobalLib::getImageUrl();
            $objPHPExcel->getActiveSheet()->mergeCells('A1:I2');
            $objPHPExcel->getActiveSheet()->setCellValue('A1', "Báo Cáo Bán Hàng Từ Ngày ".$fromdate." Đến Ngày ".$todate);
            $style_alignment = array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A3:I" . $rowCount)->getAlignment()->applyFromArray($style_alignment);
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,
                    ),
                ),
            );
            $styledataArray = array(
                'font' => array(
                    'bold' => true,
                    'color' => array('rgb' => '080808'),
                    'size' => 15,
                    'name' => 'Times New Roman'
            ));
            $styletitleArray=array(
                'font' => array(
                    'bold' => true,
                    'color' => array('rgb' => '080808'),
                    'size' => 10,
                    'name' => 'Times New Roman')
            );
             $objPHPExcel->getActiveSheet()->getStyle('F4:F'. ($rowCount - 1))->getNumberFormat()->setFormatCode("dd-mm-yyyy");
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->applyFromArray($style_alignment);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styledataArray);
            $objPHPExcel->getActiveSheet()->getStyle('A3:I3')->applyFromArray($styletitleArray);
            $objPHPExcel->getActiveSheet()->getStyle('A3:I' . ($rowCount - 1))->applyFromArray($styleArray);
            foreach (range('A', 'I') as $columnID) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
            }
            $filename='BaoCaoBanHangTuNgay'.$fromdate.'DenNgay'.$todate.'.xls';
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename='.$filename);
            header('Cache-Control: max-age=0');
            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
            $objWriter->save('php://output');
            exit();
            } else if($cat ==2){
                $nam=$this->_getParam("nam","");
                $agent_id=$this->_getParam("agent_id","");
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            $rowCount = 4;
            $stt=1;
            $objPHPExcel->getActiveSheet()->setCellValue('A3', "STT");
            $objPHPExcel->getActiveSheet()->setCellValue('B3', "Tên Khách Hàng");
            $objPHPExcel->getActiveSheet()->setCellValue('C3', "Tháng 1");
            $objPHPExcel->getActiveSheet()->setCellValue('D3', "Tháng 2");
            $objPHPExcel->getActiveSheet()->setCellValue('E3', "Tháng 3");
            $objPHPExcel->getActiveSheet()->setCellValue('F3', "Tháng 4");
            $objPHPExcel->getActiveSheet()->setCellValue('G3', "Tháng 5");
            $objPHPExcel->getActiveSheet()->setCellValue('H3', "Tháng 6");
            $objPHPExcel->getActiveSheet()->setCellValue('I3', "Tháng 7");
            $objPHPExcel->getActiveSheet()->setCellValue('J3', "Tháng 8");
            $objPHPExcel->getActiveSheet()->setCellValue('K3', "Tháng 9");
            $objPHPExcel->getActiveSheet()->setCellValue('L3', "Tháng 10");
            $objPHPExcel->getActiveSheet()->setCellValue('M3', "Tháng 11");
            $objPHPExcel->getActiveSheet()->setCellValue('N3', "Tháng 12");
            $objPHPExcel->getActiveSheet()->setCellValue('O3', "Tổng");
            $objPHPExcel->getActiveSheet()->setCellValue('P3', "Điểm");
            foreach ($this->report4model->fetchAllReportExportByKinhDoanh($nam,$this->kinhdoanh_id, $agent_id) as $items) {
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, $stt);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $rowCount, $items->name_customer);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $items->month1);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $rowCount, $items->month2);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $rowCount, $items->month3);
                $objPHPExcel->getActiveSheet()->setCellValue('F' . $rowCount, $items->month4);
                $objPHPExcel->getActiveSheet()->setCellValue('G' . $rowCount, $items->month5);
                $objPHPExcel->getActiveSheet()->setCellValue('H' . $rowCount, $items->month6);
                $objPHPExcel->getActiveSheet()->setCellValue('I' . $rowCount, $items->month7);
                $objPHPExcel->getActiveSheet()->setCellValue('J' . $rowCount, $items->month8);
                $objPHPExcel->getActiveSheet()->setCellValue('K' . $rowCount, $items->month9);
                $objPHPExcel->getActiveSheet()->setCellValue('L' . $rowCount, $items->month10);
                $objPHPExcel->getActiveSheet()->setCellValue('M' . $rowCount, $items->month11);
                $objPHPExcel->getActiveSheet()->setCellValue('N' . $rowCount, $items->month12);
                $objPHPExcel->getActiveSheet()->setCellValue('O' . $rowCount, $items->total_quantity);
                $objPHPExcel->getActiveSheet()->setCellValue('P' . $rowCount, $items->charges);
                $rowCount++;
                $stt++;
            }
            $urlimage = GlobalLib::getImageUrl();
            $objPHPExcel->getActiveSheet()->mergeCells('A1:P2');
//            $objDrawing = new PHPExcel_Worksheet_Drawing();
//            $objDrawing->setPath('../../image/images answer.png');
//            $objDrawing->setCoordinates('A1');
//            $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
//            $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(120);
            $objPHPExcel->getActiveSheet()->setCellValue('A1', "Báo Cáo Doanh Số Theo Tháng Của Năm ".$nam);
            $style_alignment = array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A3:P" . $rowCount)->getAlignment()->applyFromArray($style_alignment);
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,
                    ),
                ),
            );
            $styledataArray = array(
                'font' => array(
                    'bold' => true,
                    'color' => array('rgb' => '080808'),
                    'size' => 15,
                    'name' => 'Times New Roman'
            ));
            $styletitleArray=array(
                'font' => array(
                    'bold' => true,
                    'color' => array('rgb' => '080808'),
                    'size' => 10,
                    'name' => 'Times New Roman')
            );
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->applyFromArray($style_alignment);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styledataArray);
            $objPHPExcel->getActiveSheet()->getStyle('A3:P3')->applyFromArray($styletitleArray);
            $objPHPExcel->getActiveSheet()->getStyle('A3:P' . ($rowCount - 1))->applyFromArray($styleArray);
            foreach (range('A', 'P') as $columnID) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
            }
            $filename='BaoCaoDoanhSoTheoThangCuaNam '.$nam.'.xls';
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename='.$filename);
            header('Cache-Control: max-age=0');
            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
            $objWriter->save('php://output');
            exit();
            } else if($cat==3){
                 $nam=$this->_getParam("nam","");
                 $agent_id=$this->_getParam("agent_id","");
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->setActiveSheetIndex(0);
            $rowCount = 4;
            $stt=1;
            $objPHPExcel->getActiveSheet()->setCellValue('A3', "STT");
            $objPHPExcel->getActiveSheet()->setCellValue('B3', "Tên Khách Hàng");
            $objPHPExcel->getActiveSheet()->setCellValue('C3', "Quý 1");
            $objPHPExcel->getActiveSheet()->setCellValue('D3', "Quý 2");
            $objPHPExcel->getActiveSheet()->setCellValue('E3', "Quý 3");
            $objPHPExcel->getActiveSheet()->setCellValue('F3', "Quý 4");
            $objPHPExcel->getActiveSheet()->setCellValue('G3', "Năm");
            $objPHPExcel->getActiveSheet()->setCellValue('H3', "Điểm");
            foreach ($this->report5model->fetchAllReportExportByKinhDoanh($nam,$this->kinhdoanh_id, $agent_id) as $items) {
                $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, $stt);
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $rowCount, $items->name_customer);
                $objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $items->quarter1);
                $objPHPExcel->getActiveSheet()->setCellValue('D' . $rowCount, $items->quarter2);
                $objPHPExcel->getActiveSheet()->setCellValue('E' . $rowCount, $items->quarter3);
                $objPHPExcel->getActiveSheet()->setCellValue('F' . $rowCount, $items->quarter4);
                $objPHPExcel->getActiveSheet()->setCellValue('G' . $rowCount, $items->total_quantity);
                $objPHPExcel->getActiveSheet()->setCellValue('H' . $rowCount, $items->charges);
                $rowCount++;
                $stt++;
            }
            $objPHPExcel->getActiveSheet()->mergeCells('A1:H2');
            $objPHPExcel->getActiveSheet()->setCellValue('A1', "Báo Cáo Doanh Số Theo Quý Của Năm ".$nam);
            $style_alignment = array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle("A3:H" . $rowCount)->getAlignment()->applyFromArray($style_alignment);
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN,
                    ),
                ),
            );
            $styledataArray = array(
                'font' => array(
                    'bold' => true,
                    'color' => array('rgb' => '080808'),
                    'size' => 15,
                    'name' => 'Times New Roman'
            ));
            $styletitleArray=array(
                'font' => array(
                    'bold' => true,
                    'color' => array('rgb' => '080808'),
                    'size' => 10,
                    'name' => 'Times New Roman')
            );
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->applyFromArray($style_alignment);
            $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray($styledataArray);
            $objPHPExcel->getActiveSheet()->getStyle('A3:H3')->applyFromArray($styletitleArray);
            $objPHPExcel->getActiveSheet()->getStyle('A3:H' . ($rowCount - 1))->applyFromArray($styleArray);
            foreach (range('A', 'H') as $columnID) {
                $objPHPExcel->getActiveSheet()->getColumnDimension($columnID)->setAutoSize(true);
            }
            $filename='BaoCaoDoanhSoTheoQuýCuaNam '.$nam.'.xls';
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename='.$filename);
            header('Cache-Control: max-age=0');
            $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
            $objWriter->save('php://output');
            exit();
            }
        }
    }
}
